/* For detailed instructions, see README file */

OPTIONS SOURCE MACROGEN SYMBOLGEN MPRINT COMPRESS=YES REUSE=YES SORTSIZE= MAX;




/*=========================================*/
/*Select path where files are saved - Please see README for more details*/
libname OP "INSERT PATH";
/*Select path to save logs - Please see README for more details*/
%let LOG_PATH= INSERT PATH\\Table2.log;
options dlcreatedir; 
%PUT &LOG_PATH;
/*=========================================*/






proc printto log="&LOG_PATH" new;
run;





ODS GRAPHICS OFF;
ODS NORESULTS;
ODS SELECT NONE;
FOOTNOTE " ";
ODS TITLE " ";
/*=========================================*/

DATA TRADE_DATA;
	SET OP.OPRA_Pseudo;

	YEAR= YEAR(DATE);
	MONTH= MONTH(DATE);

	year_month = input(cats(year,month),yymmn6.);
	format year_month yymmn6.;

	IF MISSING(DMM_IDENTITY)=0 &  DMMP_IND=1 & DMM_IDENTITY^="NoDMM";


	
	

KEEP

AUCTION_IND
PImp_C
DMM_IDENTITY
DATE
PFOF_IND
YEAR_MONTH
;

RUN;



proc tabulate data=OP.Rule_606 format=10.2 OUT=DMM_MEANS_2(

rename=
(
PCTSUM_CONTRACT_Mean = PCTSUM_CONTRACT
PCTSUM_Dollar_Mean = PCTSUM_Dollar
Total_ContractS_Mean = Total_Contracts
Avg_paid_c_per_contr_Mean=Avg_paid_c_per_contr



PCTSUM_CONTRACT_Mean = PCTSUM_CONTRACT
PCTSUM_Dollar_Mean = PCTSUM_Dollar
Total_ContractS_Mean = Total_Contracts
Total_Dollar_Mean = Total_Dollar
Avg_paid_c_per_contr_Mean=Avg_paid_c_per_contr



Marketable_PCTSUM_CONTRACT_Mean = Marketable_PCTSUM_CONTRACT
Marketable_PCTSUM_Dollar_Mean = Marketable_PCTSUM_Dollar
Marketable_ContractS_Mean = Marketable_Contracts
Marketable_Dollar_Mean = Marketable_Dollar
Marketable_paid_c_per_contr_Mean=Marketable_paid_c_per_contr



NMLO_PCTSUM_CONTRACT_Mean = NMLO_PCTSUM_CONTRACT
NMLO_PCTSUM_Dollar_Mean = NMLO_PCTSUM_Dollar
NMLO_ContractS_Mean = NMLO_Contracts
NMLO_Dollar_Mean = NMLO_Dollar
NMLO_paid_c_per_contr_Mean=NMLO_paid_c_per_contr



OO_PCTSUM_CONTRACT_Mean = OO_PCTSUM_CONTRACT
OO_PCTSUM_Dollar_Mean = OO_PCTSUM_Dollar
OO_ContractS_Mean = OO_Contracts
OO_Dollar_Mean = OO_Dollar
OO_paid_c_per_contr_Mean=OO_paid_c_per_contr

)

);

CLASS DMM_IDENTITY ;
	
;



VAR Total_Contracts PCTSUM_CONTRACT PCTSUM_DOLLAR  Avg_paid_c_per_contr
Marketable_Contracts Marketable_Dollar Marketable_PCTSUM_CONTRACT Marketable_PCTSUM_DOLLAR Marketable_paid_c_per_contr
NMLO_Contracts NMLO_Dollar NMLO_PCTSUM_CONTRACT NMLO_PCTSUM_DOLLAR NMLO_paid_c_per_contr
OO_Contracts OO_Dollar OO_PCTSUM_CONTRACT OO_PCTSUM_DOLLAR OO_paid_c_per_contr 


;

	
;

TABLES 


DMM_IDENTITY *PCTSUM_CONTRACT*(MEAN)
DMM_IDENTITY *PCTSUM_Dollar*(MEAN)
DMM_IDENTITY *Total_Contracts*(MEAN)
DMM_IDENTITY *Avg_paid_c_per_contr*(MEAN)


DMM_IDENTITY *Marketable_PCTSUM_CONTRACT*(MEAN)
DMM_IDENTITY *Marketable_PCTSUM_Dollar*(MEAN)
DMM_IDENTITY *Marketable_Contracts*(MEAN)
DMM_IDENTITY *Marketable_Dollar*(MEAN)
DMM_IDENTITY *Marketable_paid_c_per_contr*(MEAN)


DMM_IDENTITY *NMLO_PCTSUM_CONTRACT*(MEAN)
DMM_IDENTITY *NMLO_PCTSUM_Dollar*(MEAN)
DMM_IDENTITY *NMLO_Contracts*(MEAN)
DMM_IDENTITY *NMLO_Dollar*(MEAN)
DMM_IDENTITY *NMLO_paid_c_per_contr*(MEAN)



DMM_IDENTITY *OO_PCTSUM_CONTRACT*(MEAN)
DMM_IDENTITY *OO_PCTSUM_Dollar*(MEAN)
DMM_IDENTITY *OO_Contracts*(MEAN)
DMM_IDENTITY *OO_Dollar*(MEAN)
DMM_IDENTITY *OO_paid_c_per_contr*(MEAN)



;


 RUN;


PROC SORT DATA=DMM_MEANS_2;BY DMM_IDENTITY;RUN;
DATA DMM_MEANS_2;
	SET DMM_MEANS_2;
	BY DMM_IDENTITY;
	IF DMM_IDENTITY = "Morgan Stanley" THEN DMM_IDENTITY="Morgan";

	DROP _TYPE_ _PAGE_ _TABLE_;
RUN;

PROC SQL;
	CREATE TABLE DESCRIPTIVE
	AS SELECT  B.DMM_IDENTITY AS DMM_IDENTITY_2 , B.PCTSUM_CONTRACT AS PERC_CONTRACT_Pr, B.PCTSUM_Dollar as PFOF_perc, B.Total_Contracts/1000000 AS Contract_purc, 
	B.Avg_paid_c_per_contr,


	B.Marketable_PCTSUM_CONTRACT AS Marketable_PERC_CONTRACT_Pr,
	B.Marketable_PCTSUM_Dollar as Marketable_PFOF_perc,
	B.Marketable_Contracts/1000000 AS Marketable_Contract_purc,
	B.Marketable_paid_c_per_contr,
	B.Marketable_Dollar/1000000 as Marketable_PFOF_D,


	B.NMLO_PCTSUM_CONTRACT AS NMLO_PERC_CONTRACT_Pr,
	B.NMLO_PCTSUM_Dollar as NMLO_PFOF_perc,
	B.NMLO_Contracts/1000000 AS NMLO_Contract_purc,
	B.NMLO_paid_c_per_contr,
	B.NMLO_Dollar/1000000 as NMLO_PFOF_D,


	
	B.OO_PCTSUM_CONTRACT AS OO_PERC_CONTRACT_Pr,
	B.OO_PCTSUM_Dollar as OO_PFOF_perc,
	B.OO_Contracts/1000000 AS OO_Contract_purc,
	B.OO_paid_c_per_contr,
	B.OO_Dollar/1000000 as OO_PFOF_D



	FROM DMM_MEANS_2 B;
QUIT;

DATA DESCRIPTIVE;
	SET DESCRIPTIVE;
	

	IF PERC_CONTRACT_Pr = . THEN PERC_CONTRACT_Pr=0;
	IF PFOF_perc = .  THEN PFOF_perc=0;
	IF Contract_purc=.  THEN Contract_purc=0;
	IF PFOF_D=.  THEN PFOF_D=0;
	IF Avg_paid_c_per_contr=.  THEN Avg_paid_c_per_contr=0;

	IF  Marketable_PERC_CONTRACT_Pr = . THEN Marketable_PERC_CONTRACT_Pr=0;
	IF Marketable_PFOF_perc = .  THEN Marketable_PFOF_perc=0;
	IF Marketable_Contract_purc=.  THEN Marketable_Contract_purc=0;
	IF Marketable_PFOF_D=.  THEN Marketable_PFOF_D=0;
	IF Marketable_paid_c_per_contr=.  THEN Marketable_paid_c_per_contr=0;

	IF  NMLO_PERC_CONTRACT_Pr = . THEN NMLO_PERC_CONTRACT_Pr=0;
	IF NMLO_PFOF_perc = .  THEN NMLO_PFOF_perc=0;
	IF NMLO_Contract_purc=.  THEN NMLO_Contract_purc=0;
	IF NMLO_PFOF_D=.  THEN NMLO_PFOF_D=0;
	IF NMLO_paid_c_per_contr=.  THEN NMLO_paid_c_per_contr=0;

	IF  OO_PERC_CONTRACT_Pr = . THEN OO_PERC_CONTRACT_Pr=0;
	IF OO_PFOF_perc = .  THEN OO_PFOF_perc=0;
	IF OO_Contract_purc=.  THEN OO_Contract_purc=0;
	IF OO_PFOF_D=.  THEN OO_PFOF_D=0;
	IF OO_paid_c_per_contr=.  THEN OO_paid_c_per_contr=0;






RUN;




/* PRICE IMPR & AUCTION FREQ BY WS*/


PROC SORT DATA=TRADE_DATA; BY YEAR_MONTH;RUN;
proc tabulate data=TRADE_DATA format=10.2 OUT=SUMS(
RENAME=(

Auction_IND_SUM=Auction_IND
))
;
VAR 
Auction_IND
PIMP_C
;
CLASS DMM_IDENTITY	
;
TABLES 

DMM_IDENTITY*Auction_IND*(SUM)

;
BY YEAR_MONTH;
RUN;

proc tabulate data=TRADE_DATA format=10.2 OUT=MEANS_BY_TRADE(
RENAME=(

PIMP_C_MEAN = PIMP_C_BY_TRADE
))
;
VAR 
PIMP_C
;
CLASS DMM_IDENTITY	
;
TABLES 

DMM_IDENTITY*PIMP_C*(MEAN)


;
RUN;




PROC SORT DATA=TRADE_DATA; BY YEAR_MONTH;RUN;
proc tabulate data=TRADE_DATA format=10.2 OUT=MEANS(
RENAME=(

PIMP_C_MEAN = PIMP_C
))
;
VAR 
PIMP_C
;
CLASS DMM_IDENTITY	
;
TABLES 

DMM_IDENTITY*PIMP_C*(MEAN)
;
RUN;

proc tabulate data=SUMS format=10.2 OUT=PCTSUM(
RENAME=(

Auction_IND_PctSum_0_Auction_IND = AUCTION_PCTSUM
))
;
VAR 
Auction_IND
;
CLASS DMM_IDENTITY	
;
TABLES 

DMM_IDENTITY*Auction_IND*(PCTSUM)
;
RUN;


DATA PCTSUM;
	SET PCTSUM;
	DROP _TABLE_ _PAGE_ _TYPE_;
RUN;


DATA MEANS;
	SET MEANS;
	DROP _TABLE_ _PAGE_ _TYPE_;
RUN;



PROC SORT DATA=MEANS;BY DMM_IDENTITY;RUN;
PROC SORT DATA=PCTSUM;BY DMM_IDENTITY;RUN;
PROC SORT DATA=MEANS_BY_TRADE; BY DMM_IDENTITY;RUN;

DATA MERGED;
	MERGE MEANS PCTSUM MEANS_BY_TRADE;
	BY DMM_IDENTITY;
RUN;







proc sql;
	create table DESCRIPTIVE_2 AS SELECT
	a.*,B.PIMP_C_BY_TRADE,  B.AUCTION_PCTSUM, B.DMM_IDENTITY 
	from DESCRIPTIVE A FULL JOIN MERGED B
		ON A.DMM_IDENTITY_2=B.DMM_IDENTITY;
QUIT;













DATA DESCRIPTIVE_2;
	SET DESCRIPTIVE_2;

/*	NPFOF DMMs*/
	IF MISSING(DMM_IDENTITY_2)=1 THEN DMM_IDENTITY_2 = PUT(DMM_IDENTITY,$38.);

	IF MISSING(Marketable_PERC_CONTRACT_Pr)=1 THEN Marketable_PERC_CONTRACT_Pr=0;
	IF MISSING(Marketable_PFOF_perc)=1 THEN Marketable_PFOF_perc=0;
	IF MISSING(Marketable_Contract_purc)=1 THEN Marketable_Contract_purc=0;
	IF MISSING(Marketable_PFOF_D)=1 THEN Marketable_PFOF_D=0;
	IF MISSING(Marketable_paid_c_per_contr)=1 THEN Marketable_paid_c_per_contr=0;

	IF MISSING(NMLO_PERC_CONTRACT_Pr) THEN NMLO_PERC_CONTRACT_Pr=0;
	IF MISSING(NMLO_PFOF_perc)=1 THEN NMLO_PFOF_perc=0;
	IF MISSING(NMLO_Contract_purc)=1 THEN NMLO_Contract_purc=0;
	IF MISSING(NMLO_PFOF_D)=1 THEN NMLO_PFOF_D=0;
	IF MISSING(NMLO_paid_c_per_contr)=1 THEN NMLO_paid_c_per_contr=0;

	IF MISSING(OO_PERC_CONTRACT_Pr)=1 THEN OO_PERC_CONTRACT_Pr=0;
	IF MISSING(OO_PFOF_perc)=1 THEN OO_PFOF_perc=0;
	IF MISSING(OO_Contract_purc)=1 THEN OO_Contract_purc=0;
	IF MISSING(OO_PFOF_D)=1 THEN OO_PFOF_D=0;
	IF MISSING(OO_paid_c_per_contr)=1 THEN OO_paid_c_per_contr=0;






Marketable_PERC_CONTRACT_Pr_Char =PUT(Marketable_PERC_CONTRACT_Pr , 8.2);
Marketable_PFOF_perc_Char =PUT(Marketable_PFOF_perc , 8.2);
Marketable_Contract_purc_Char =PUT(Marketable_Contract_purc , 8.2);
Marketable_PFOF_D_Char =PUT(Marketable_PFOF_D , 8.2);
Marketable_paid_c_per_contr_char=  PUT(Marketable_paid_c_per_contr, 8.2);


NMLO_PERC_CONTRACT_Pr_Char =PUT(NMLO_PERC_CONTRACT_Pr , 8.2);
NMLO_PFOF_perc_Char =PUT(NMLO_PFOF_perc , 8.2);
NMLO_Contract_purc_Char =PUT(NMLO_Contract_purc , 8.2);
NMLO_PFOF_D_Char =PUT(NMLO_PFOF_D , 8.2);
NMLO_paid_c_per_contr_char=  PUT(NMLO_paid_c_per_contr, 8.2);



OO_PERC_CONTRACT_Pr_Char =PUT(OO_PERC_CONTRACT_Pr , 8.2);
OO_PFOF_perc_Char =PUT(OO_PFOF_perc , 8.2);
OO_Contract_purc_Char =PUT(OO_Contract_purc , 8.2);
OO_PFOF_D_Char =PUT(OO_PFOF_D , 8.2);
OO_paid_c_per_contr_char=  PUT(OO_paid_c_per_contr, 8.2);






AUCTION_PCTSUM_CHAR =PUT(AUCTION_PCTSUM,  comma8.2);
PIMP_C_BY_TRADE_CHAR =PUT(PIMP_C_BY_TRADE, comma8.2);



if DMM_IDENTITY_2 IN ("NYSE", "Cboe", "NASDAQ","NoDMM") THEN DELETE;
if DMM_IDENTITY_2="NoDMM" THEN DMM_IDENTITY_2=PUT("No Data",$128.);


/*=================================================*/
/*Option and underlying symbols are anonymized*/
D_COUNTS_SYMBOL_ONLY_CHAR=PUT(" " , $12.);
PCTSUM_SYMBOLS_CHAR=PUT(" " , $12.);
SENDER_COUNT_Char =PUT(" " , $12.);
U_OPTIONS_Char = PUT(" ", $12.);
OPTIONS_Char =PUT(" ", $12.);
PCTSUM_U_OPTIONS_Char =PUT(" ", $12.);
PCTSUM_OPTIONS_Char =PUT(" ", $12.);
/*=================================================*/


RUN;




PROC SORT DATA=DESCRIPTIVE_2; BY descending PFOF_perc ;RUN;





ODS GRAPHICS OFF;
ODS RESULTS;
ODS SELECT ALL;
ODS TITLE "TABLE 2: Wholesaler Summary (Pseudo Data) - PANEL A";
FOOTNOTE "This table presents payment for orderflow (PFOF) and wholesaler assignment metrics    
 using Rule 606 data and pseudo OPRA data. Due to data restrictions, options and stock symbols are removed; all columns with counts  
 have been set to missing ";
PROC REPORT data=DESCRIPTIVE_2;
 COLUMNS

DMM_IDENTITY_2






		Marketable_PFOF_D_Char 
		Marketable_PFOF_perc_Char 
		Marketable_paid_c_per_contr_char
		Marketable_Contract_purc_cHAR
		Marketable_PERC_CONTRACT_Pr_Char


			 ;
		DEFINE DMM_IDENTITY_2  /DISPLAY "DMM" format =$168.;
		DEFINE  Marketable_PFOF_D_Char  /DISPLAY "Marketable PFOF (\$M)" format =$168.;
		DEFINE  Marketable_PFOF_perc_Char /DISPLAY "Marketable PFOF(\%)" format =$168.;
		DEFINE Marketable_paid_c_per_contr_char /DISPLAY "Marketable PFOF (Per 100)" format =$168.;
		DEFINE Marketable_Contract_purc_cHAR /DISPLAY "Marketable Contract Purch. (M.)" format =$168.;
		DEFINE  Marketable_PERC_CONTRACT_Pr_Char /DISPLAY "Marketable Contract Purch. (\%)" format =$168.;







		


run;


ODS GRAPHICS OFF;
ODS RESULTS;
ODS SELECT ALL;
ODS TITLE "TABLE 2: Wholesaler Summary (Pseudo Data) - PANEL B";
FOOTNOTE "This table presents payment for orderflow (PFOF) and wholesaler assignment metrics    
 using Rule 606 data and pseudo OPRA data. Due to data restrictions, options and stock symbols are removed; all columns with counts  
 have been set to missing ";
PROC REPORT data=DESCRIPTIVE_2;
 COLUMNS

DMM_IDENTITY_2






		NMLO_PFOF_D_Char 
		NMLO_PFOF_perc_Char 
		NMLO_paid_c_per_contr_char
		NMLO_Contract_purc_cHAR
		NMLO_PERC_CONTRACT_Pr_Char


			 ;
		DEFINE DMM_IDENTITY_2  /DISPLAY "DMM" format =$168.;
		DEFINE  NMLO_PFOF_D_Char  /DISPLAY "NMLO PFOF (\$M)" format =$168.;
		DEFINE  NMLO_PFOF_perc_Char /DISPLAY "NMLO PFOF(\%)" format =$168.;
		DEFINE NMLO_paid_c_per_contr_char /DISPLAY "NMLO PFOF (Per 100)" format =$168.;
		DEFINE NMLO_Contract_purc_cHAR /DISPLAY "NMLO Contract Purch. (M.)" format =$168.;
		DEFINE  NMLO_PERC_CONTRACT_Pr_Char /DISPLAY "NMLO Contract Purch. (\%)" format =$168.;







		


run;





ODS GRAPHICS OFF;
ODS RESULTS;
ODS SELECT ALL;
ODS TITLE "TABLE 2: Wholesaler Summary (Pseudo Data) - PANEL C";
FOOTNOTE "This table presents payment for orderflow (PFOF) and wholesaler assignment metrics    
 using Rule 606 data and pseudo OPRA data.  
 Due to data restrictions, options and stock symbols are removed; all columns with counts  
 have been set to missing ";
PROC REPORT data=DESCRIPTIVE_2;
 COLUMNS

		DMM_IDENTITY_2


		OO_PFOF_D_Char 
		OO_PFOF_perc_Char 
		OO_paid_c_per_contr_char
		OO_Contract_purc_cHAR
		OO_PERC_CONTRACT_Pr_Char;

		DEFINE DMM_IDENTITY_2  /DISPLAY "DMM" format =$168.;
		DEFINE  OO_PFOF_D_Char  /DISPLAY "OO PFOF (\$M)" format =$168.;
		DEFINE  OO_PFOF_perc_Char /DISPLAY "OO PFOF(\%)" format =$168.;
		DEFINE OO_paid_c_per_contr_char /DISPLAY "OO PFOF (Per 100)" format =$168.;
		DEFINE OO_Contract_purc_cHAR /DISPLAY "OO Contract Purch. (M.)" format =$168.;
		DEFINE  OO_PERC_CONTRACT_Pr_Char /DISPLAY "OO Contract Purch. (\%)" format =$168.;


	

run;






ODS GRAPHICS OFF;
ODS RESULTS;
ODS SELECT ALL;
ODS TITLE "TABLE 2: Wholesaler Summary (Pseudo Data) - PANEL D";
FOOTNOTE "This table presents payment for orderflow (PFOF) and wholesaler assignment metrics    
 using Rule 606 data and pseudo OPRA data. This table presents payment for orderflow  
 (PFOF) and wholesaler assignment metrics using Rule 606 data and pseudo OPRA data.   
 Due to data restrictions, options and stock symbols are removed; all columns with counts  
 have been set to missing ";
PROC REPORT data=DESCRIPTIVE_2;
 COLUMNS

			DMM_IDENTITY_2

			SENDER_COUNT_Char
			D_COUNTS_SYMBOL_ONLY_CHAR
			PCTSUM_SYMBOLS_CHAR
			OPTIONS_Char
			PCTSUM_OPTIONS_Char


			AUCTION_PCTSUM_CHAR
			PIMP_C_BY_TRADE_CHAR









			 ;
			DEFINE 	DMM_IDENTITY_2  /DISPLAY "DMM" format =$168.;
			DEFINE  SENDER_COUNT_Char /DISPLAY "Exchanges (\#)" format =$168.;
			DEFINE 	PCTSUM_SYMBOLS_CHAR /DISPLAY "Assignments (%)" format =$168.;
			DEFINE  OPTIONS_Char  /DISPLAY "Options (\#)" format =$168.;
			DEFINE  PCTSUM_OPTIONS_Char   /DISPLAY "Options (%)" format =$168.;
			DEFINE  D_COUNTS_SYMBOL_ONLY_CHAR   /DISPLAY "\# Symbols" format =$168.;
			DEFINE  AUCTION_PCTSUM_CHAR   /DISPLAY "Auctions (%)" format =$168.;
			DEFINE  D_COUNTS_SYMBOL_ONLY_CHAR   /DISPLAY "Auction PImpr" format =$168.;
			DEFINE  PIMP_C_BY_TRADE_CHAR  /DISPLAY "Price Improvement" format =$168.;	


run;
/*=========================================*/
FOOTNOTE " ";
ODS TITLE " ";




proc printto log=log;
run;







